﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Data;


namespace StoreManagementSystem.DataAccessLayer
{
    public class SQLDataProvider : StoreManagementSystem.DataAccessLayer.ISQLDataProvider
    {
        private DbConnection connection;
        private DbProviderFactory factory;
        private string connectionName;

        public SQLDataProvider(string connName)
        {
            connectionName = connName;
        }

        public string getConnectionName()
        {
            return connectionName;
        }

        /// <summary>
        /// Opens a specified database connection based on the connection string name defined in the Web.config.
        /// </summary>
        /// <param name="connstr"></param>
        public void Open()
        {
            //get the information out of the configuration file.
            ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionName];
            factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);

            //create a connection of the proper type.
            connection = factory.CreateConnection();
            //set the connection string
            connection.ConnectionString = connectionStringSettings.ConnectionString;

            //open the connection
            connection.Open();
        }

        public void Open(string connstr, string providername)
        {
            factory = DbProviderFactories.GetFactory(providername);

            //create a connection of the proper type.
            connection = factory.CreateConnection();
            //set the connection string
            connection.ConnectionString = connstr;

            //open the connection
            connection.Open();
        }

        /// <summary>
        /// Closes the current database connection.
        /// </summary>
        public void Close()
        {
            try
            {
                connection.Close();
            }
            catch (Exception e)
            {

            }
        }

        /// <summary>
        /// Executes SQL on the currently open database.
        /// </summary>
        /// <param name="SQL">SQL to be passed to the database</param>
        /// <param name="results">Specify whether results will be returned by the query</param>
        /// <param name="dbparams">Array of SQL parameters</param>
        /// <returns></returns>
        public DataTable ExecuteSQL(string SQL, List<SQLParam> dbparams, bool results)
        {
            
            DataTable dt = new DataTable();
            DbCommand command = connection.CreateCommand();
            command.Connection = connection;
            command.CommandText = SQL;
            //command.CommandType = CommandType.Text;
            for (int i = 0; i < dbparams.Count; i++)
            {
                DbParameter param = command.CreateParameter();
                param.ParameterName = dbparams[i].Name;
                param.Value = dbparams[i].Value;
                command.Parameters.Add(param);
            }

            try
            {
                if (results)
                {
                    DbDataAdapter da = factory.CreateDataAdapter();
                    da.SelectCommand = command;
                    da.Fill(dt);
                    return dt;
                }
                else
                {
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
            }
            return null;
        }

        public DataTable ExecuteSQL(string SQL, bool results)
        {
            DataTable dt = new DataTable();
            DbCommand command = connection.CreateCommand();
            command.Connection = connection;
            command.CommandText = SQL;
            command.CommandType = CommandType.Text;

            try
            {

                if (results)
                {
                    DbDataAdapter da = factory.CreateDataAdapter();
                    da.SelectCommand = command;
                    da.Fill(dt);
                    return dt;
                }
                else
                {
                    command.ExecuteNonQuery();
                    
                }
            }
            catch (Exception e)
            {
            }
            return null;
        }
    }
}